using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Alf.Site.Entidades;

namespace MakeIT.Alf.Site.Negocios
{
	public partial class AlfDAO : DAO
	{

		public static ImovelxcaracteristicaVO getImovelxcaracteristica(int? idImovelXCaracteristica, string obs)
		{
			ImovelxcaracteristicaVO imovelxcaracteristicaVO = null;
			List<ImovelxcaracteristicaVO> listImovelxcaracteristicaVOs = getImovelxcaracteristicas(idImovelXCaracteristica, obs, null, null);
			if (listImovelxcaracteristicaVOs != null && listImovelxcaracteristicaVOs.Count > 0)
			{
				imovelxcaracteristicaVO = listImovelxcaracteristicaVOs[0];
			}
			return imovelxcaracteristicaVO;
		}

		public static ImovelxcaracteristicaVO getImovelxcaracteristica(int? idImovelXCaracteristica)
		{
			return getImovelxcaracteristica(idImovelXCaracteristica, null);
		}

		public static List<ImovelxcaracteristicaVO> getImovelxcaracteristicas()
		{
			return getImovelxcaracteristicas(null, null, null, null);
		}

		public static List<ImovelxcaracteristicaVO> getImovelxcaracteristicas(int? idImovelXCaracteristica)
		{
			return getImovelxcaracteristicas(idImovelXCaracteristica, null, null, null);
		}

		public static List<ImovelxcaracteristicaVO> getImovelxcaracteristicas(int? idImovelXCaracteristica, string obs)
		{
			return getImovelxcaracteristicas(idImovelXCaracteristica, obs, null, null);
		}

		public static List<ImovelxcaracteristicaVO> getImovelxcaracteristicas(int? idImovelXCaracteristica, string obs, int? idImovel, int? idCaracteristica)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT imoc.idImovel as imoc_idImovel, imo2.titulo as imo2_titulo, imoc.idCaracteristica as imoc_idCaracteristica, car.caracteristica as car_caracteristica, imoc.obs as imoc_obs, imoc.idImovelXCaracteristica as imoc_idImovelXCaracteristica, imoc.Cadastro as imoc_Cadastro, imoc.CadastradoPor as imoc_CadastradoPor, imoc.Edicao as imoc_Edicao, imoc.EditadoPor as imoc_EditadoPor  ");
			query.AppendLine("FROM imovelxcaracteristica as imoc LEFT OUTER JOIN imovel as imo2 on imoc.idImovel=imo2.idImovel  LEFT OUTER JOIN caracteristica as car on imoc.idCaracteristica=car.idCaracteristica  ");
			if (idImovel != null)
			{
				query.AppendLine(where + "imoc.idImovel=@idImovel ");
				where = "AND ";
			}
			if (idCaracteristica != null)
			{
				query.AppendLine(where + "imoc.idCaracteristica=@idCaracteristica ");
				where = "AND ";
			}
			if (obs != null)
			{
				query.AppendLine(where + "imoc.obs LIKE @obs ");
				where = "AND ";
			}
			if (idImovelXCaracteristica != null)
			{
				query.AppendLine(where + "imoc.idImovelXCaracteristica=@idImovelXCaracteristica ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY imoc.obs ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idImovel!= null) cmd.Parameters.Add(getParameter(cmd, "@idImovel", idImovel));
			if (idCaracteristica!= null) cmd.Parameters.Add(getParameter(cmd, "@idCaracteristica", idCaracteristica));
			if (obs!= null) cmd.Parameters.Add(getParameter(cmd, "@obs", "%"+obs+"%"));
			if (idImovelXCaracteristica!= null) cmd.Parameters.Add(getParameter(cmd, "@idImovelXCaracteristica", idImovelXCaracteristica));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ImovelxcaracteristicaVO> listImovelxcaracteristica = new List<ImovelxcaracteristicaVO>();

				while (reader.Read())
				{
					ImovelxcaracteristicaVO imovelxcaracteristicaVO = ImovelxcaracteristicaVO.CreateVO();
					imovelxcaracteristicaVO.idImovel = reader.GetInt32(reader.GetOrdinal("imoc_idImovel"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo2_titulo")))
						imovelxcaracteristicaVO.idImovelVO.titulo = reader.GetString(reader.GetOrdinal("imo2_titulo"));
					imovelxcaracteristicaVO.idCaracteristica = reader.GetInt32(reader.GetOrdinal("imoc_idCaracteristica"));
					if (!reader.IsDBNull(reader.GetOrdinal("car_caracteristica")))
						imovelxcaracteristicaVO.idCaracteristicaVO.caracteristica = reader.GetString(reader.GetOrdinal("car_caracteristica"));
					if (!reader.IsDBNull(reader.GetOrdinal("imoc_obs")))
						imovelxcaracteristicaVO.obs = reader.GetString(reader.GetOrdinal("imoc_obs"));
					imovelxcaracteristicaVO.idImovelXCaracteristica = reader.GetInt32(reader.GetOrdinal("imoc_idImovelXCaracteristica"));
					imovelxcaracteristicaVO.cadastro = reader.GetDateTime(reader.GetOrdinal("imoc_Cadastro"));
					imovelxcaracteristicaVO.cadastradoPor = reader.GetString(reader.GetOrdinal("imoc_CadastradoPor"));
					imovelxcaracteristicaVO.edicao = reader.GetDateTime(reader.GetOrdinal("imoc_Edicao"));
					imovelxcaracteristicaVO.editadoPor = reader.GetString(reader.GetOrdinal("imoc_EditadoPor"));


					listImovelxcaracteristica.Add(imovelxcaracteristicaVO);
				}
				reader.Close();
				return listImovelxcaracteristica;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setImovelxcaracteristica(ImovelxcaracteristicaVO imovelxcaracteristicaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelxcaracteristica ");
			query.AppendLine("SET idImovel=@idImovel, idCaracteristica=@idCaracteristica, obs=@obs,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idImovelXCaracteristica=@idImovelXCaracteristica ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovel", imovelxcaracteristicaVO.idImovel));
			cmd.Parameters.Add(getParameter(cmd, "@idCaracteristica", imovelxcaracteristicaVO.idCaracteristica));
			if (imovelxcaracteristicaVO.obs == null)
				cmd.Parameters.Add(getParameter(cmd, "@obs", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@obs", imovelxcaracteristicaVO.obs));
			cmd.Parameters.Add(getParameter(cmd, "@idImovelXCaracteristica", imovelxcaracteristicaVO.idImovelXCaracteristica));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelxcaracteristicaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelxcaracteristicaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelxcaracteristicaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelxcaracteristicaVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newImovelxcaracteristica(ImovelxcaracteristicaVO imovelxcaracteristicaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO imovelxcaracteristica ");
			query.AppendLine("  ( idImovel, idCaracteristica, obs, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @idImovel, @idCaracteristica, @obs, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovel", imovelxcaracteristicaVO.idImovel));
			cmd.Parameters.Add(getParameter(cmd, "@idCaracteristica", imovelxcaracteristicaVO.idCaracteristica));
			if (imovelxcaracteristicaVO.obs == null)
				cmd.Parameters.Add(getParameter(cmd, "@obs", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@obs", imovelxcaracteristicaVO.obs));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelxcaracteristicaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelxcaracteristicaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelxcaracteristicaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelxcaracteristicaVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('imovelxcaracteristica') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delImovelxcaracteristica(ImovelxcaracteristicaVO imovelxcaracteristicaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM imovelxcaracteristica ");
			query.AppendLine("WHERE idImovelXCaracteristica=@idImovelXCaracteristica ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelXCaracteristica", imovelxcaracteristicaVO.idImovelXCaracteristica));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
		public static int getCountImovelxcaracteristicaxIdImovel(int? idImovel)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idImovel) as ocorrencias FROM imovelxcaracteristica WHERE IdImovel=@IdImovel ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdImovel", idImovel));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
		public static int getCountImovelxcaracteristicaxIdCaracteristica(int? idCaracteristica)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCaracteristica) as ocorrencias FROM imovelxcaracteristica WHERE IdCaracteristica=@IdCaracteristica ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCaracteristica", idCaracteristica));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
	}
}
